﻿create procedure [dbo].[usp_SqlErrorInsert]
	@SqlErrorId int = 0 output
as
begin
	set nocount on;
	
	set @SqlErrorId = 0;
	
	begin try
		-- return if there is no error information to log.
		if error_number() is null
			return;
		
		-- return if inside an uncommittable transaction.
		-- data insertion/modification is not allowed when a transaction is in an uncommittable state.	
		if xact_state() = -1
		begin
			print 'cannot log SQL error since the current transaction is in an uncommittable state, rollback the transaction before executing usp_SqlErrorInsert in order to successfully log error information.';
            return;
		end
	
		insert [dbo].[SqlError]
		(
			[ErrorNumber]
			, [ErrorSeverity]
			, [ErrorState]
			, [ErrorProcedure]
			, [ErrorLine]
			, [ErrorMessage]
			, [User]
		)
		values
		(
            error_number()
			, error_severity()
			, error_state()
			, error_procedure()
			, error_line()
			, error_message()
			, convert(sysname, CURRENT_USER)
		);
		
		select @SqlErrorId = scope_identity();
		
	end try
	begin catch
		print 'an error occurred in stored procedure usp_SqlErrorInsert';
		execute [dbo].[usp_PrintSqlError]
		return -1	
	end catch
end;
